﻿/*
* hr_employee.cs
*
* 功 能： N/A
* 类 名： hr_employee
*
* Ver    变更日期             负责人     变更内容
* ───────────────────────────────────
* V1.0  2015-06-24 11:20:12    黄润伟    
*
* Copyright (c) 2015 www.xhdcrm.com   All rights reserved.
*┌──────────────────────────────────┐
*│　版权所有：黄润伟                      　　　　　　　　　　　　　　│
*└──────────────────────────────────┘
*/

using System;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using XHD.DBUtility;

//Please add references

namespace XHD.DAL
{
    /// <summary>
    ///     数据访问类:hr_employee
    /// </summary>
    public class hr_employee
    {
        #region  BasicMethod

        /// <summary>
        ///     增加一条数据
        /// </summary>
        public int Add(Model.hr_employee model)
        {
            var strSql = new StringBuilder();
            strSql.Append("insert into hr_employee(");
            strSql.Append("uid,pwd,name,idcard,birthday,d_id,postid,email,sex,tel,status,position_id,sort,EntryDate,address,remarks,education,professional,schools,title,isDelete,Delete_time,portal,theme,canlogin)");
            strSql.Append(" values (");
            strSql.Append("@uid,@pwd,@name,@idcard,@birthday,@d_id,@postid,@email,@sex,@tel,@status,@position_id,@sort,@EntryDate,@address,@remarks,@education,@professional,@schools,@title,@isDelete,@Delete_time,@portal,@theme,@canlogin)");
            strSql.Append(";select @@IDENTITY");
            SqlParameter[] parameters =
            {
                new SqlParameter("@uid", SqlDbType.VarChar, 50),
                new SqlParameter("@pwd", SqlDbType.VarChar, 50),
                new SqlParameter("@name", SqlDbType.VarChar, 50),
                new SqlParameter("@idcard", SqlDbType.VarChar, 50),
                new SqlParameter("@birthday", SqlDbType.VarChar, 50),
                new SqlParameter("@d_id", SqlDbType.Int, 4),
                new SqlParameter("@postid", SqlDbType.Int, 4),
                new SqlParameter("@email", SqlDbType.VarChar, 50),
                new SqlParameter("@sex", SqlDbType.VarChar, 50),
                new SqlParameter("@tel", SqlDbType.VarChar, 50),
                new SqlParameter("@status", SqlDbType.VarChar, 50),
                new SqlParameter("@position_id", SqlDbType.Int, 4),
                new SqlParameter("@sort", SqlDbType.Int, 4),
                new SqlParameter("@EntryDate", SqlDbType.VarChar, 50),
                new SqlParameter("@address", SqlDbType.VarChar, 255),
                new SqlParameter("@remarks", SqlDbType.VarChar, 255),
                new SqlParameter("@education", SqlDbType.VarChar, 50),
                new SqlParameter("@professional", SqlDbType.VarChar, 50),
                new SqlParameter("@schools", SqlDbType.VarChar, 50),
                new SqlParameter("@title", SqlDbType.VarChar, 50),
                new SqlParameter("@isDelete", SqlDbType.Int, 4),
                new SqlParameter("@Delete_time", SqlDbType.DateTime),
                new SqlParameter("@portal", SqlDbType.VarChar, 250),
                new SqlParameter("@theme", SqlDbType.VarChar, 250),
                new SqlParameter("@canlogin", SqlDbType.Int, 4)
            };
            parameters[0].Value = model.uid;
            parameters[1].Value = model.pwd;
            parameters[2].Value = model.name;
            parameters[3].Value = model.idcard;
            parameters[4].Value = model.birthday;
            parameters[5].Value = model.d_id;
            parameters[6].Value = model.postid;
            parameters[7].Value = model.email;
            parameters[8].Value = model.sex;
            parameters[9].Value = model.tel;
            parameters[10].Value = model.status;
            parameters[11].Value = model.position_id;
            parameters[12].Value = model.sort;
            parameters[13].Value = model.EntryDate;
            parameters[14].Value = model.address;
            parameters[15].Value = model.remarks;
            parameters[16].Value = model.education;
            parameters[17].Value = model.professional;
            parameters[18].Value = model.schools;
            parameters[19].Value = model.title;
            parameters[20].Value = model.isDelete;
            parameters[21].Value = model.Delete_time;
            parameters[22].Value = model.portal;
            parameters[23].Value = model.theme;
            parameters[24].Value = model.canlogin;

            object obj = DbHelperSQL.GetSingle(strSql.ToString(), parameters);
            if (obj == null)
            {
                return 0;
            }
            return Convert.ToInt32(obj);
        }

        /// <summary>
        ///     更新一条数据
        /// </summary>
        public bool Update(Model.hr_employee model)
        {
            var strSql = new StringBuilder();
            strSql.Append("update hr_employee set ");
            strSql.Append("uid=@uid,");
            strSql.Append("pwd=@pwd,");
            strSql.Append("name=@name,");
            strSql.Append("idcard=@idcard,");
            strSql.Append("birthday=@birthday,");
            strSql.Append("d_id=@d_id,");
            strSql.Append("postid=@postid,");
            strSql.Append("email=@email,");
            strSql.Append("sex=@sex,");
            strSql.Append("tel=@tel,");
            strSql.Append("status=@status,");
            strSql.Append("position_id=@position_id,");
            strSql.Append("sort=@sort,");
            strSql.Append("EntryDate=@EntryDate,");
            strSql.Append("address=@address,");
            strSql.Append("remarks=@remarks,");
            strSql.Append("education=@education,");
            strSql.Append("professional=@professional,");
            strSql.Append("schools=@schools,");
            strSql.Append("title=@title,");
            strSql.Append("portal=@portal,");
            strSql.Append("theme=@theme,");
            strSql.Append("canlogin=@canlogin");
            strSql.Append(" where ID=@ID");
            SqlParameter[] parameters =
            {
                new SqlParameter("@uid", SqlDbType.VarChar, 50),
                new SqlParameter("@pwd", SqlDbType.VarChar, 50),
                new SqlParameter("@name", SqlDbType.VarChar, 50),
                new SqlParameter("@idcard", SqlDbType.VarChar, 50),
                new SqlParameter("@birthday", SqlDbType.VarChar, 50),
                new SqlParameter("@d_id", SqlDbType.Int, 4),
                new SqlParameter("@postid", SqlDbType.Int, 4),
                new SqlParameter("@email", SqlDbType.VarChar, 50),
                new SqlParameter("@sex", SqlDbType.VarChar, 50),
                new SqlParameter("@tel", SqlDbType.VarChar, 50),
                new SqlParameter("@status", SqlDbType.VarChar, 50),
                new SqlParameter("@position_id", SqlDbType.Int, 4),
                new SqlParameter("@sort", SqlDbType.Int, 4),
                new SqlParameter("@EntryDate", SqlDbType.VarChar, 50),
                new SqlParameter("@address", SqlDbType.VarChar, 255),
                new SqlParameter("@remarks", SqlDbType.VarChar, 255),
                new SqlParameter("@education", SqlDbType.VarChar, 50),
                new SqlParameter("@professional", SqlDbType.VarChar, 50),
                new SqlParameter("@schools", SqlDbType.VarChar, 50),
                new SqlParameter("@title", SqlDbType.VarChar, 50),
                new SqlParameter("@portal", SqlDbType.VarChar, 250),
                new SqlParameter("@theme", SqlDbType.VarChar, 250),
                new SqlParameter("@canlogin", SqlDbType.Int, 4),
                new SqlParameter("@ID", SqlDbType.Int, 4)
            };
            parameters[0].Value = model.uid;
            parameters[1].Value = model.pwd;
            parameters[2].Value = model.name;
            parameters[3].Value = model.idcard;
            parameters[4].Value = model.birthday;
            parameters[5].Value = model.d_id;
            parameters[6].Value = model.postid;
            parameters[7].Value = model.email;
            parameters[8].Value = model.sex;
            parameters[9].Value = model.tel;
            parameters[10].Value = model.status;
            parameters[11].Value = model.position_id;
            parameters[12].Value = model.sort;
            parameters[13].Value = model.EntryDate;
            parameters[14].Value = model.address;
            parameters[15].Value = model.remarks;
            parameters[16].Value = model.education;
            parameters[17].Value = model.professional;
            parameters[18].Value = model.schools;
            parameters[19].Value = model.title;
            parameters[20].Value = model.portal;
            parameters[21].Value = model.theme;
            parameters[22].Value = model.canlogin;
            parameters[23].Value = model.ID;

            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            return false;
        }

        /// <summary>
        ///     删除一条数据
        /// </summary>
        public bool Delete(int ID)
        {
            var strSql = new StringBuilder();
            strSql.Append("delete from hr_employee ");
            strSql.Append(" where ID=@ID");
            SqlParameter[] parameters =
            {
                new SqlParameter("@ID", SqlDbType.Int, 4)
            };
            parameters[0].Value = ID;

            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            return false;
        }

        /// <summary>
        ///     批量删除数据
        /// </summary>
        public bool DeleteList(string IDlist)
        {
            var strSql = new StringBuilder();
            strSql.Append("delete from hr_employee ");
            strSql.Append(" where ID in (" + IDlist + ")  ");
            int rows = DbHelperSQL.ExecuteSql(strSql.ToString());
            if (rows > 0)
            {
                return true;
            }
            return false;
        }


        /// <summary>
        ///     得到一个对象实体
        /// </summary>
        public Model.hr_employee GetModel(int ID)
        {
            var strSql = new StringBuilder();
            strSql.Append(
                "select  top 1 ID,uid,pwd,name,idcard,birthday,d_id,postid,email,sex,tel,status,position_id,sort,EntryDate,address,remarks,education,default_city,professional,schools,title,isDelete,Delete_time,portal,theme,canlogin from hr_employee ");
            strSql.Append(" where ID=@ID");
            SqlParameter[] parameters =
            {
                new SqlParameter("@ID", SqlDbType.Int, 4)
            };
            parameters[0].Value = ID;

            var model = new Model.hr_employee();
            DataSet ds = DbHelperSQL.Query(strSql.ToString(), parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                return DataRowToModel(ds.Tables[0].Rows[0]);
            }
            return null;
        }


        /// <summary>
        ///     得到一个对象实体
        /// </summary>
        public Model.hr_employee DataRowToModel(DataRow row)
        {
            var model = new Model.hr_employee();
            if (row != null)
            {
                if (row["ID"] != null && row["ID"].ToString() != "")
                {
                    model.ID = int.Parse(row["ID"].ToString());
                }
                if (row["uid"] != null)
                {
                    model.uid = row["uid"].ToString();
                }
                if (row["pwd"] != null)
                {
                    model.pwd = row["pwd"].ToString();
                }
                if (row["name"] != null)
                {
                    model.name = row["name"].ToString();
                }
                if (row["idcard"] != null)
                {
                    model.idcard = row["idcard"].ToString();
                }
                if (row["birthday"] != null)
                {
                    model.birthday = row["birthday"].ToString();
                }
                if (row["d_id"] != null && row["d_id"].ToString() != "")
                {
                    model.d_id = int.Parse(row["d_id"].ToString());
                }
                if (row["postid"] != null && row["postid"].ToString() != "")
                {
                    model.postid = int.Parse(row["postid"].ToString());
                }
                if (row["email"] != null)
                {
                    model.email = row["email"].ToString();
                }
                if (row["sex"] != null)
                {
                    model.sex = row["sex"].ToString();
                }
                if (row["tel"] != null)
                {
                    model.tel = row["tel"].ToString();
                }
                if (row["status"] != null)
                {
                    model.status = row["status"].ToString();
                }
                if (row["position_id"] != null && row["position_id"].ToString() != "")
                {
                    model.position_id = int.Parse(row["position_id"].ToString());
                }
                if (row["sort"] != null && row["sort"].ToString() != "")
                {
                    model.sort = int.Parse(row["sort"].ToString());
                }
                if (row["EntryDate"] != null)
                {
                    model.EntryDate = row["EntryDate"].ToString();
                }
                if (row["address"] != null)
                {
                    model.address = row["address"].ToString();
                }
                if (row["remarks"] != null)
                {
                    model.remarks = row["remarks"].ToString();
                }
                if (row["education"] != null)
                {
                    model.education = row["education"].ToString();
                }
                if (row["default_city"] != null)
                {
                    model.default_city = row["default_city"].ToString();
                }
                if (row["professional"] != null)
                {
                    model.professional = row["professional"].ToString();
                }
                if (row["schools"] != null)
                {
                    model.schools = row["schools"].ToString();
                }
                if (row["title"] != null)
                {
                    model.title = row["title"].ToString();
                }
                if (row["isDelete"] != null && row["isDelete"].ToString() != "")
                {
                    model.isDelete = int.Parse(row["isDelete"].ToString());
                }
                if (row["Delete_time"] != null && row["Delete_time"].ToString() != "")
                {
                    model.Delete_time = DateTime.Parse(row["Delete_time"].ToString());
                }
                if (row["portal"] != null)
                {
                    model.portal = row["portal"].ToString();
                }
                if (row["theme"] != null)
                {
                    model.theme = row["theme"].ToString();
                }
                if (row["canlogin"] != null && row["canlogin"].ToString() != "")
                {
                    model.canlogin = int.Parse(row["canlogin"].ToString());
                }
            }
            return model;
        }

        /// <summary>
        ///     获得数据列表
        /// </summary>
        public DataSet GetList(string strWhere)
        {
            var strSql = new StringBuilder();
            strSql.Append("select ID,uid,name,idcard,birthday,d_id,postid,email,sex,tel,status,position_id,sort,EntryDate,address,remarks,education,professional,schools,title,isDelete,Delete_time,portal,theme,canlogin,default_city ");
            strSql.Append(" ,(select d_name from hr_department where id = hr_employee.[d_id]) as [dname]  ");
            strSql.Append(" ,(select post_name from hr_post where id = hr_employee.[postid]) as [post]  ");
            strSql.Append(" ,(select position_name from hr_position where id = hr_employee.[position_id]) as position  ");
            strSql.Append(" FROM hr_employee ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            return DbHelperSQL.Query(strSql.ToString());
        }

        /// <summary>
        ///     获得前几行数据
        /// </summary>
        public DataSet GetList(int Top, string strWhere, string filedOrder)
        {
            var strSql = new StringBuilder();
            strSql.Append("select ");
            if (Top > 0)
            {
                strSql.Append(" top " + Top);
            }
            strSql.Append(" ID,uid,name,idcard,birthday,d_id,postid,email,sex,tel,status,position_id,sort,EntryDate,address,remarks,education,professional,schools,title,isDelete,Delete_time,portal,theme,canlogin,default_city ");
            strSql.Append(" ,(select d_name from hr_department where id = hr_employee.[d_id]) as [dname]  ");
            strSql.Append(" ,(select post_name from hr_post where id = hr_employee.[postid]) as [post]  ");
            strSql.Append(" ,(select position_name from hr_position where id = hr_employee.[position_id]) as position  ");
            strSql.Append(" FROM hr_employee ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            strSql.Append(" order by " + filedOrder);
            return DbHelperSQL.Query(strSql.ToString());
        }

        /// <summary>
        ///     分页获取数据列表
        /// </summary>
        public DataSet GetList(int PageSize, int PageIndex, string strWhere, string filedOrder, out string Total)
        {
            var strSql_grid = new StringBuilder();
            var strSql_total = new StringBuilder();
            strSql_total.Append(" SELECT COUNT(ID) FROM hr_employee ");
            strSql_grid.Append("SELECT ");
            strSql_grid.Append("ID,uid,name,idcard,birthday,d_id,postid,email,sex,tel,status,position_id,sort,EntryDate,address,remarks,education,professional,schools,title,isDelete,Delete_time,portal,theme,canlogin ");
            strSql_grid.Append(" ,(select d_name from hr_department where id = w1.[d_id]) as [dname]  ");
            strSql_grid.Append(" ,(select post_name from hr_post where id = w1.[postid]) as [post]  ");
            strSql_grid.Append(" ,(select position_name from hr_position where id = w1.[position_id]) as position  ");
            strSql_grid.Append(
                " FROM ( SELECT ID,uid,pwd,name,idcard,birthday,d_id,postid,email,sex,tel,status,position_id,sort,EntryDate,address,remarks,education,professional,schools,title,isDelete,Delete_time,portal,theme,canlogin, ROW_NUMBER() OVER( Order by " +
                filedOrder + " ) AS n from hr_employee");
            if (strWhere.Trim() != "")
            {
                strSql_grid.Append(" WHERE " + strWhere);
                strSql_total.Append(" WHERE " + strWhere);
            }
            strSql_grid.Append("  ) as w1  ");
            strSql_grid.Append("WHERE n BETWEEN " + PageSize*(PageIndex - 1) + " AND " + PageSize*PageIndex);
            strSql_grid.Append(" ORDER BY " + filedOrder);
            Total = DbHelperSQL.Query(strSql_total.ToString()).Tables[0].Rows[0][0].ToString();
            return DbHelperSQL.Query(strSql_grid.ToString());
        }

        #endregion  BasicMethod

        #region  ExtensionMethod

        /// <summary>
        ///     获取密码
        /// </summary>
        public DataSet GetPWD(int ID)
        {
            var strSql = new StringBuilder();
            strSql.Append("select pwd ");
            strSql.Append(" FROM hr_employee ");
            strSql.Append(" WHERE ID =" + ID);

            return DbHelperSQL.Query(strSql.ToString());
        }

        public bool changepwd(Model.hr_employee model)
        {
            var strSql = new StringBuilder();
            strSql.Append("update hr_employee set ");
            strSql.Append("pwd=@pwd");
            strSql.Append(" where ID=@ID");
            SqlParameter[] parameters =
            {
                new SqlParameter("@pwd", SqlDbType.VarChar, 50),
                new SqlParameter("@ID", SqlDbType.Int, 4)
            };

            parameters[0].Value = model.pwd;
            parameters[1].Value = model.ID;

            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            return false;
        }

        /// <summary>
        ///     更新岗位
        /// </summary>
        public bool UpdatePost(Model.hr_employee model)
        {
            var strSql = new StringBuilder();
            strSql.Append("update hr_employee set ");

            strSql.Append("d_id=@d_id,");
            strSql.Append("postid=@postid,");
            strSql.Append("position_id=@position_id");
            strSql.Append(" where ID=@ID");
            SqlParameter[] parameters =
            {
                new SqlParameter("@d_id", SqlDbType.Int, 4),
                new SqlParameter("@postid", SqlDbType.Int, 4),
                new SqlParameter("@position_id", SqlDbType.Int, 4),
                new SqlParameter("@ID", SqlDbType.Int, 4)
            };

            parameters[0].Value = model.d_id;
            parameters[1].Value = model.postid;
            parameters[2].Value = model.position_id;
            parameters[3].Value = model.ID;

            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            return false;
        }
        /// <summary>
        ///     更新默认城市
        /// </summary>
        public bool UpdateDefaultCity(Model.hr_employee model)
        {
            var strSql = new StringBuilder();
            strSql.Append("update hr_employee set ");

            strSql.Append("default_city=@default_city");
            strSql.Append(" where ID=@ID");
            SqlParameter[] parameters =
            {
                new SqlParameter("@default_city", SqlDbType.VarChar, 50),
                new SqlParameter("@ID", SqlDbType.Int, 4)
            };

            parameters[0].Value = model.default_city;
            parameters[1].Value = model.ID;

            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            return false;
        }


        /// <summary>
        ///     获取角色
        /// </summary>
        public DataSet GetRole(int ID)
        {
            var strSql = new StringBuilder();
            strSql.Append("select * from Sys_role where RoleID in ");
            strSql.Append("(select RoleID from Sys_role_emp where empID=" + ID + ")  ");

            return DbHelperSQL.Query(strSql.ToString());
        }

        /// <summary>
        ///     个人信息修改
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public bool PersonalUpdate(Model.hr_employee model)
        {
            var strSql = new StringBuilder();
            strSql.Append("update hr_employee set ");
            strSql.Append("name=@name,");
            strSql.Append("idcard=@idcard,");
            strSql.Append("birthday=@birthday,");
            strSql.Append("email=@email,");
            strSql.Append("sex=@sex,");
            strSql.Append("tel=@tel,");
            strSql.Append("address=@address,");
            strSql.Append("education=@education,");
            strSql.Append("professional=@professional,");
            strSql.Append("schools=@schools,");
            strSql.Append("title=@title");
            strSql.Append(" where ID=@ID");
            SqlParameter[] parameters =
            {
                new SqlParameter("@name", SqlDbType.VarChar, 50),
                new SqlParameter("@idcard", SqlDbType.VarChar, 50),
                new SqlParameter("@birthday", SqlDbType.VarChar, 50),
                new SqlParameter("@email", SqlDbType.VarChar, 50),
                new SqlParameter("@sex", SqlDbType.VarChar, 50),
                new SqlParameter("@tel", SqlDbType.VarChar, 50),
                new SqlParameter("@address", SqlDbType.VarChar, 255),
                new SqlParameter("@education", SqlDbType.VarChar, 50),
                new SqlParameter("@professional", SqlDbType.VarChar, 50),
                new SqlParameter("@schools", SqlDbType.VarChar, 50),
                new SqlParameter("@title", SqlDbType.VarChar, 50),
                new SqlParameter("@ID", SqlDbType.Int, 4)
            };
            parameters[0].Value = model.name;
            parameters[1].Value = model.idcard;
            parameters[2].Value = model.birthday;
            parameters[3].Value = model.email;
            parameters[4].Value = model.sex;
            parameters[5].Value = model.tel;
            parameters[6].Value = model.address;
            parameters[7].Value = model.education;
            parameters[8].Value = model.professional;
            parameters[9].Value = model.schools;
            parameters[10].Value = model.title;
            parameters[11].Value = model.ID;

            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            return false;
        }

        #endregion  ExtensionMethod
    }
}